package com.github.dengmin.mysql.controller;

import com.github.dengmin.mysql.dto.ConnectionDto;
import com.github.dengmin.mysql.dto.ExecuteSql;
import com.github.dengmin.mysql.model.ApiResponse;
import com.github.dengmin.mysql.model.DatabaseConnection;
import com.github.dengmin.mysql.model.Pagination;
import com.github.dengmin.mysql.service.DatabaseConnectionService;
import com.github.dengmin.mysql.service.DbMetaDataService;
import com.github.dengmin.mysql.service.SQLExecute;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.validation.Valid;
import java.util.*;

/**
 * @Author dengmin
 * @Created 2020/6/5 下午5:30
 */
@CrossOrigin
@RestController
@RequestMapping("/db")
public class DataBaseController {
    private static final long serialVersionUID = 1L;

    @Resource
    private DbMetaDataService dbMetaDataService;

    @Resource
    private SQLExecute sqlExecute;
    @Resource
    private DatabaseConnectionService connectionService;

    /**
     * 查询所有的数据库资源
     * @return
     */
    @GetMapping("/configs")
    public ApiResponse getDbConfigs(){
        List<Map<String,String>> results = new ArrayList<>();
        Pagination<DatabaseConnection> page = connectionService.pagination(1, 50);
        for(DatabaseConnection connection : page.getData()){
            Map<String, String> map = new HashMap<>();
            map.put("id", connection.getId()+"");
            map.put("text", connection.getName());
            results.add(map);
        }
        return ApiResponse.ok(results);
    }

    @GetMapping("/schemas")
    public ApiResponse getDbs(int resourceId){
        return ApiResponse.ok(dbMetaDataService.getSchemas(resourceId));
    }

    @GetMapping("/instance/{dbName}")
    public ApiResponse getTableMenu(@PathVariable("dbName") String dbName, int resourceId){
        return ApiResponse.ok(dbMetaDataService.getTableMenu(dbName, resourceId));
    }

    @GetMapping("/tables/{dbName}")
    public ApiResponse getTables(@PathVariable("dbName") String dbName, int resourceId){
        return ApiResponse.ok(dbMetaDataService.getTables(dbName, resourceId));
    }

    @GetMapping("/table/all")
    public Object getColumns(String dbName, String table, Integer pageNo, Integer pageSize){
        if(pageNo == null){
            pageNo = 1;
        }
        if(pageSize == null){
            pageSize = 50;
        }
        Map<String, Object> results = new HashMap<>();
        results.put("columns", dbMetaDataService.queryTableColumns(dbName, table));
        results.put("rows", dbMetaDataService.getTableData(dbName, table, pageNo, pageSize));
        results.put("total", dbMetaDataService.getCount(dbName, table));
        return results;
    }

    /**
     * 执行SQＬ
     * @param sqlObj
     * @return
     */
    @PostMapping("/execute")
    public ApiResponse execute(@Valid @RequestBody ExecuteSql sqlObj){
        return ApiResponse.ok(sqlExecute.execute(sqlObj));
    }

    /**
     * 数据库连接
     * @param pageNo 当前第几页
     * @param pageSize　　每页多少条数据
     * @return
     */
    @GetMapping("/connections")
    public ApiResponse connections(int pageNo, int pageSize){
        return ApiResponse.ok(connectionService.pagination(pageNo, pageSize));
    }

    @PostMapping("/connection/save")
    public ApiResponse saveConnection(@RequestBody ConnectionDto dto){
        DatabaseConnection connection = new DatabaseConnection();
        connection.setName(dto.getName());
        connection.setHost(dto.getHost());
        connection.setPort(dto.getPort());
        connection.setUsername(dto.getUsername());
        connection.setPassword(dto.getPassword());
        connection.setDbName(dto.getDbName());
        int id = connectionService.save(connection);
        connection.setId(id);
        return ApiResponse.ok(connection);
    }

    @DeleteMapping("/connection/{id}")
    public ApiResponse delConnection(@PathVariable("id") Integer id){
        connectionService.deleteById(id);
        return ApiResponse.ok();
    }


}
